﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DevCore.DB;
using DevCore.Log;
using DevCore.Utility;

namespace DevCore.Service.Db
{
    /// <summary>
    /// 数据库数据操作
    /// </summary>
    public class DbDataService
    {
        #region 执行存储过程相关

        /// <summary>
        /// 执行存储过程得到结果.
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="dbParamInfos">存储过程参数值</param>
        /// <returns></returns>
        public static int ExecuteProc(String procName, List<DbParamInfo> dbParamInfos)
        {
            int retValue = -1;

            try
            { 
                SqlHelper dbHelper = new SqlHelper();
                dbHelper.Open();
                SqlCommand cmd = dbHelper.GetProcDbCommand(procName);
                dbHelper.AddProcParameters(cmd, dbParamInfos);
                retValue = dbHelper.ExecuteNonQuery(cmd);
                SetOutputParams(cmd, dbParamInfos);
                dbHelper.Close();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }

            return retValue;
        }

        /// <summary>
        /// 执行存储过程得到表结果
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="dbParamInfos">存储过程参数值</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTableProc(String procName, List<DbParamInfo> dbParamInfos)
        {
            DataTable dtValue = null;
            try
            {
                SqlHelper dbHelper = new SqlHelper();
                dbHelper.Open();
                SqlCommand cmd = dbHelper.GetProcDbCommand(procName);
                dbHelper.AddProcParameters(cmd, dbParamInfos);
                dtValue = dbHelper.ExecuteDataTable(cmd);
                SetOutputParams(cmd, dbParamInfos);
                dbHelper.Close();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
            return dtValue;
        }

        /// <summary>
        /// 设置输出参数
        /// </summary>
        /// <param name="cmd">当前Command对象</param>
        /// <param name="dbParamInfos">当前参数集合</param>
        private static void SetOutputParams(SqlCommand cmd, List<DbParamInfo> dbParamInfos)
        {
            foreach (DbParamInfo info in dbParamInfos.FindAll(delegate(DbParamInfo dbParamInfo){
                                                                                                   return
                                                                                                       dbParamInfo.
                                                                                                           Direction ==
                                                                                                       1; }))
            {
                info.Value = cmd.Parameters["@po_" + info.Name].Value;
            }
        }

        /// <summary>
        /// 得到一个参数名的具体值
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="dicParams">参数列表</param>
        /// <returns></returns>
        public static object GetDbParamValue(String name, List<DbParamInfo> dicParams)
        {
            foreach (DbParamInfo dbParamInfo in dicParams)
            {
                if (dbParamInfo.Name.ToLower().Equals(name.ToLower()))
                {
                    return dbParamInfo.Value;
                    break;
                }
            }
            return null;
        }

        #endregion



        #region 执行Sql语句

        /// <summary>
        /// 执行Sql
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <returns></returns>
        public static int ExecuteSql(String strSql)
        {
            LogHelper.WriteLog(strSql);
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(strSql);
            //dbHelper.AddSqlParam(cmd, dicParams);
            int rValue = dbHelper.ExecuteNonQuery(cmd);
            dbHelper.Close();
            return rValue;
        }



        #endregion



        #region Sql语句直接操作数据库




        /// <summary>
        /// 向表中插入数据
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="dicParams">字段参数</param>
        /// <returns></returns>
        public static int InsertBySql(String tbName, List<DbParamInfo> dicParams)
        {
            String tmpSql = "";
            tmpSql = GetInsertSql(tbName, dicParams);
            LogHelper.WriteLog(tmpSql);
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(tmpSql);
            dbHelper.AddSqlParam(cmd, dicParams);
            int rValue = dbHelper.ExecuteNonQuery(cmd);
            dbHelper.Close();
            return rValue;

        }

        /// <summary>
        /// 更新表中数据
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="lstColumnInfo"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static int UpdateBySql(String tbName, List<DbParamInfo> lstColumnInfo, String strWhere)
        {
            int rValue = -1;
            String tmpSql = "";
            tmpSql = GetUpdateSql(tbName, lstColumnInfo, strWhere);
            LogHelper.WriteLog(tmpSql);
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(tmpSql);
            dbHelper.AddSqlParam(cmd, lstColumnInfo);
            rValue = dbHelper.ExecuteNonQuery(cmd);
            dbHelper.Close();
            return rValue;
        }

        /// <summary>
        ///根据表名及条件删除数据
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public static int DeleteBySql(String tbName, String strWhere)
        {
            int rValue = -1;
            String tmpSql = "";
            if (String.IsNullOrEmpty(strWhere))
            {
                tmpSql = "delete from " + tbName;
            }
            else
            {
                tmpSql = "delete from " + tbName + " where " + strWhere;
            }

            LogHelper.WriteLog(tmpSql);
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(tmpSql);
            rValue = dbHelper.ExecuteNonQuery(cmd);
            dbHelper.Close();
            return rValue;
        }

        /// <summary>
        /// 根据Sql得到表
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="columnNames">字段名</param>
        /// <param name="strWhere">条件</param>
        /// <param name="sOrderByColumn">排序字段</param>
        /// <returns></returns>
        public static DataTable GetRowsBySql(String tbName, String columnNames, String strWhere, String sOrderByColumn)
        {
            DataTable dtInfo = new DataTable();
            String tmpSql = "";
            tmpSql = GetRowSql(tbName, columnNames, strWhere, sOrderByColumn);
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(tmpSql);
            dtInfo = dbHelper.ExecuteDataTable(cmd);
            dbHelper.Close();
            return dtInfo;
        }

        /// <summary>
        /// 得到指定条件的记录数
        /// </summary>
        /// <param name="tbName">表名或者视图名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public static long GetAllCount(string tbName, String strWhere)
        {
            long rValue = 0;
            String tmpSql = "";
            if (String.IsNullOrEmpty(strWhere))
            {
                tmpSql = "select count(*) from " + tbName;
            }
            else
            {
                tmpSql = "select count(*) from " + tbName + " where " + strWhere;
            }
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(tmpSql);
            object ret = dbHelper.ExecuteScalar(cmd, tmpSql);
            rValue = ret == null ? 0 : Convert.ToInt32(ret);
            return rValue;
        }

        /// <summary>
        /// 根据Sql得到及条件得数据表
        /// </summary>
        /// <param name="columnNames">列名组合，以,分隔开</param>
        /// <param name="strWhere">条件</param>
        /// <param name="sqlString">Sql语句查询语句</param>
        /// <param name="sOrderByColumn">排序字段</param>
        /// <returns></returns>
        public static DataTable GetRowsBySqlString(String sqlString, String columnNames, String strWhere, String sOrderByColumn)
        {
            DataTable dtInfo = new DataTable();
            String tmpSql = "";
            tmpSql = sqlString;
            SqlHelper dbHelper = new SqlHelper();
            dbHelper.Open();
            SqlCommand cmd = dbHelper.GetSqlDbCommand(tmpSql);
            dtInfo = dbHelper.ExecuteDataTable(cmd);
            dbHelper.Close();

            return dtInfo;
        }
        #endregion

        #region 构造Sql语句

        /// <summary>
        /// 得到一行数据的sql
        /// </summary>
        /// <param name="tbName">表名或者视图名</param>
        /// <param name="columnNames"></param>
        /// <param name="strWhere">条件</param>
        /// <param name="sOrderByColumn"></param>
        /// <returns></returns>
        public static String GetRowSql(String tbName, String columnNames, String strWhere, String sOrderByColumn)
        {
            String rValue = String.Empty;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select ");
            if (String.IsNullOrEmpty(columnNames))
            {
                sbSql.Append(" * ");
            }
            else
            {
                sbSql.Append(columnNames);
            }


            sbSql.Append(" from " + tbName);

            if (!String.IsNullOrEmpty(strWhere))
            {
                sbSql.Append(" where " + strWhere);
            }

            if (!String.IsNullOrEmpty(sOrderByColumn))
            {
                sbSql.Append(sOrderByColumn);
            }

            rValue = sbSql.ToString();


            return rValue;
        }

        /// <summary>
        /// 得到插入的数据库操作
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="dicParams">列参数</param>
        /// <returns></returns>
        public static String GetInsertSql(String tbName, List<DbParamInfo> dicParams)
        {
            String rValue = String.Empty;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("insert into " + tbName + " ( ");
            for (int i = 0; i < dicParams.Count; i++)
            {
                DbParamInfo curColumn = dicParams[i];
                if (i == dicParams.Count - 1)
                {
                    sbSql.Append(curColumn.Name + ")");
                }
                else
                {
                    sbSql.Append(curColumn.Name + ",");
                }

            }

            sbSql.Append(" values(");
            for (int i = 0; i < dicParams.Count; i++)
            {
                DbParamInfo curColumn = dicParams[i];
                if (i == dicParams.Count - 1)
                {
                    sbSql.Append("@" + curColumn.Name + ")");
                }
                else
                {
                    sbSql.Append("@" + curColumn.Name + ",");
                }
            }
            rValue = sbSql.ToString();
            return rValue;
        }

        /// <summary>
        /// 得到更新数据的Sql
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="lstColumnInfo">要更新的列及列值</param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static String GetUpdateSql(String tbName, List<DbParamInfo> lstColumnInfo, String strWhere)
        {
            String rValue = String.Empty;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" update " + tbName + " set ");
            for (int i = 0; i < lstColumnInfo.Count; i++)
            {
                DbParamInfo curColumn = lstColumnInfo[i];
                if (i == lstColumnInfo.Count - 1)
                {
                    sbSql.Append(curColumn.Name + "=@" + curColumn.Name);
                }
                else
                {
                    sbSql.Append(curColumn.Name + "=@" + curColumn.Name + ", ");
                }
            }


            if (!String.IsNullOrEmpty(strWhere))
            {
                sbSql.Append(" where " + strWhere);
            }

            rValue = sbSql.ToString();
            return rValue;

        }

        #endregion


        #region 存储过程
        /// <summary>
        /// 通用的分页存储过程实现方法,通用的分页存储过程为.
        /// </summary>
        /// <param name="sColumns"></param>
        /// <param name="sTableName"></param>
        /// <param name="sConditions"></param>
        /// <param name="sOrderby"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public static DataTable GetCommonList(String sColumns, String sTableName, String sConditions, String sOrderby,
                                        int pageIndex, int pageSize, out int pageCount, out long recordCount)
        {
            DataTable dtInfo = new DataTable();
            int AllPageCount = 0;
            long allCount = 0;

            List<DbParamInfo> dicParams = new List<DbParamInfo>();
            dicParams.Add(new DbParamInfo() { Name = "Columns", Direction = 0, Size = 200, DbType = "VarChar", Value = sColumns });
            dicParams.Add(new DbParamInfo() { Name = "TableName", Direction = 0, Size = 200, DbType = "VarChar", Value = sTableName });
            dicParams.Add(new DbParamInfo() { Name = "Condition", Direction = 0, Size = 200, DbType = "VarChar", Value = sConditions });
            dicParams.Add(new DbParamInfo() { Name = "OrderBy", Direction = 0, Size = 200, DbType = "VarChar", Value = sOrderby });
            dicParams.Add(new DbParamInfo() { Name = "PageIndex", Direction = 0, Size = 200, DbType = "Int", Value = pageIndex });
            dicParams.Add(new DbParamInfo() { Name = "PageSize", Direction = 0, Size = 200, DbType = "Int", Value = pageSize });

            dicParams.Add(new DbParamInfo() { Name = "PageCount", Direction = 1, Size = 200, DbType = "Int", Value = AllPageCount });
            dicParams.Add(new DbParamInfo() { Name = "RecordCount", Direction = 1, Size = 200, DbType = "BigInt", Value = allCount });


            dtInfo = ExecuteDataTableProc("App_S_spCommonPage", dicParams);
            AllPageCount = StringHelper.ObjectToT<int>(GetDbParamValue("PageCount",dicParams), AllPageCount);
            allCount = StringHelper.ObjectToT<long>(GetDbParamValue("RecordCount",dicParams), allCount);

            
            pageCount = AllPageCount;
            recordCount = allCount;
            return dtInfo;
        }




        #endregion
    }
}
